package com.css.fxfzcpxx.modules.questionData.repository.impl;

import com.css.fxfzcpxx.constants.YNEnum;
import com.css.fxfzcpxx.modules.questionData.entity.QuestionData;
import com.css.fxfzcpxx.modules.questionData.entity.UserTestHistory;
import com.css.fxfzcpxx.modules.questionData.entity.UserTsetQuestion;
import com.css.fxfzcpxx.modules.questionData.param.QuestionDataParam;
import com.css.fxfzcpxx.modules.questionData.repository.QuestionDataRepository;
import com.css.fxfzcpxx.util.PlatformObjectUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: zhangSongRui
 * @CreateTime: 2023/03/11
 * @Description: 题库管理
 */
@Repository
public class QuestionDataRepositoryImpl implements QuestionDataRepository {
    
    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    private static final String TABLE_NAME = "cpxx_question_data";
    private static final String TABLE_ID = "id"; // 主键id

    @Override
    public Map<String, Object> getPage(int curPage, int pageSize, QuestionDataParam param) {
        StringBuilder sql = new StringBuilder("select a.*,b.type_name from (select * from " + TABLE_NAME);
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0'");
        StringBuilder orderSql = new StringBuilder(" order by a.create_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        //时间范围
        if (!PlatformObjectUtils.isEmpty(param.getStartTime()) & !PlatformObjectUtils.isEmpty(param.getEndTime())) {
            whereSql.append("and Date(create_time) between '" + param.getStartTime() + "' and '" + param.getEndTime() + "'");
        }
        //大于开始时间
        if (!PlatformObjectUtils.isEmpty(param.getStartTime()) & PlatformObjectUtils.isEmpty(param.getEndTime())) {
            whereSql.append("and Date(create_time)  >='" + param.getStartTime() + "'");
        }
        //小于结束时间
        if ( !PlatformObjectUtils.isEmpty(param.getEndTime()) & PlatformObjectUtils.isEmpty(param.getStartTime())) {
            whereSql.append("and Date(create_time)  <='" + param.getEndTime() + "'");
        }
        //习题名称
        if (!PlatformObjectUtils.isEmpty(param.getQuestionName())) {
            whereSql.append(" and question_name like '%" + param.getQuestionName() + "%'");
        }
        //题目分类
        if (!PlatformObjectUtils.isEmpty(param.getTypeId())) {
            whereSql.append(" and type_id = '" + param.getTypeId() + "'");
        }
        //题目类型(0单选1多选2判断)
        if (!PlatformObjectUtils.isEmpty(param.getQuestionType())) {
            whereSql.append(" and question_type = '" + param.getQuestionType() + "'");
        }
        StringBuilder sqlb = new StringBuilder(" )a left join (select id,type_name from cpxx_type_manage where del_flag = '0') b on a.type_id = b.id ");
        sql.append(whereSql).append(sqlb).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(QuestionData.class)).size();
        List<QuestionData> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(QuestionData.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public void updateById(QuestionData param) {
        String sql = "update "+TABLE_NAME+
                " set type_id = :typeId,question_type=:questionType, " +
                "question_name = :questionName,a = :A, " +
                "b = :B,c = :C,d = :D,e = :E,f=:F,"+
                "answers = :answers,analysis = :analysis,"+
                "update_user = :updateUser,update_time = :updateTime " +
                "where del_flag = '0' and " + TABLE_ID + " = '" + param.getId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void delete(List<String> asList) {
        String sql = "update "+TABLE_NAME+" set del_flag = '"+ YNEnum.Y.toString()+"' where id in  ('" + String.join("','", asList) + "')";
        jdbcTemplate.update(sql);
    }

    @Override
    public QuestionData findById(String id) {
        String sql ="select a.*,b.type_name from (select * from " + TABLE_NAME+" where id = '"+id+"')a left join (select id,type_name from cpxx_type_manage where del_flag = '0') b on a.type_id = b.id ";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(QuestionData.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public void save(QuestionData param) {
        String sql = "insert into "+TABLE_NAME+"(id,type_id,question_type,question_name,a," +
                "b,c,d,e,f,answers,analysis,del_flag,create_user,create_time) " +
                "values(:id,:typeId,:questionType,:questionName,:A," +
                ":B,:C,:D,:E,:F,:answers,:analysis,:delFlag,:createUser, :createTime)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public List<QuestionData> queryByIds(List<String> asList) {
        String sql = "select a.*,b.type_name from (select * from "+TABLE_NAME+" where "+TABLE_ID+" in  ('" + String.join("','", asList) + "') )a left join (select id,type_name  from cpxx_type_manage where del_flag = '0') b on a.type_id = b.id ";
        List<QuestionData> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(QuestionData.class));
        return list;
    }

    @Override
    public void batchSave(List<QuestionData> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "INSERT INTO "+TABLE_NAME+" (id,type_id,question_type,question_name,a," +
                "b,c,d,e,f,answers,analysis,del_flag,create_user,create_time)" +
                "values (:id,:typeId,:questionType,:questionName,:A," +
                ":B,:C,:D,:E,:F,:answers,:analysis,:delFlag,:createUser,:createTime)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public List<QuestionData> queryQuestionByTypeId(String typeId, String s) {
        String sql = "select * from "+TABLE_NAME+" where del_flag = '0' and type_id = '"+typeId+"' and question_type = '"+s+"'";
        List<QuestionData> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(QuestionData.class));
        return list;
    }

    @Override
    public void saveUserTestHistory(UserTestHistory param) {
        String sql = "insert into cpxx_user_test_history (id,course_id,total_score,score,del_flag,create_user,create_time,create_name) " +
                "values(:id,:courseId,:totalScore,:score,:delFlag,:createUser, :createTime,:createName)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void batchSaveUserTestQuestion(List<UserTsetQuestion> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "INSERT INTO cpxx_user_tset_question (id,test_id,question_id,user_answer,correct,sequence)" +
                "values (:id,:testId,:questionId,:userAnswer,:correct,:sequence)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public UserTestHistory getMaximumScore(String courseId, String userID) {
        String sql ="select * from cpxx_user_test_history where course_id = '"+courseId+"' and create_user = '"+userID+"'  order by score desc limit 1";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(UserTestHistory.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public Integer testCheck(List<String> titleName) {
        String sql = "select id from  "+TABLE_NAME+" where question_name in  ('" + String.join("','", titleName) + "') and del_flag='0'";
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(QuestionData.class)).size();
    }

}
